﻿create PROCEDURE Sync.SP_LoanExecution_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanExecution_Temp TABLE 
		(
			LoanExecutionID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[GuarantorName] nvarchar(50),
				[LoanExecutionStatus] bit,
				[GuarantorSSN] nvarchar(50),
				[InterestPercentage] decimal,
				[LoanPaymentMethodID] uniqueidentifier,
				[LoanAmount] float,
				[LoanPeriod] int,
				[TotalLoanAmount] float,
				[ExecutionDate] datetime,
				[CheckNumber] nvarchar(50),
				[CheckDate] datetime,
				[GuarantorAddress] nvarchar(50),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanExecution_Temp 
	   SELECT	Tbl.Col.value('LoanExecutionID[1]','uniqueidentifier') LoanExecutionID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('GuarantorName[1]', 'nvarchar(50)') [GuarantorName],
Tbl.Col.value('LoanExecutionStatus[1]', 'bit') [LoanExecutionStatus],
Tbl.Col.value('GuarantorSSN[1]', 'nvarchar(50)') [GuarantorSSN],
Tbl.Col.value('InterestPercentage[1]', 'decimal') [InterestPercentage],
Tbl.Col.value('LoanPaymentMethodID[1]', 'uniqueidentifier') [LoanPaymentMethodID],
Tbl.Col.value('LoanAmount[1]', 'float') [LoanAmount],
Tbl.Col.value('LoanPeriod[1]', 'int') [LoanPeriod],
Tbl.Col.value('TotalLoanAmount[1]', 'float') [TotalLoanAmount],
Tbl.Col.value('ExecutionDate[1]', 'datetime') [ExecutionDate],
Tbl.Col.value('CheckNumber[1]', 'nvarchar(50)') [CheckNumber],
Tbl.Col.value('CheckDate[1]', 'datetime') [CheckDate],
Tbl.Col.value('GuarantorAddress[1]', 'nvarchar(50)') [GuarantorAddress],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanExecution') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanExecution] ([LoanExecutionID],[LoanID],[GuarantorName],[LoanExecutionStatus],[GuarantorSSN],[InterestPercentage],[LoanPaymentMethodID],[LoanAmount],[LoanPeriod],[TotalLoanAmount],[ExecutionDate],[CheckNumber],[CheckDate],[GuarantorAddress])
	SELECT t.[LoanExecutionID],t.[LoanID],t.[GuarantorName],t.[LoanExecutionStatus],t.[GuarantorSSN],t.[InterestPercentage],t.[LoanPaymentMethodID],t.[LoanAmount],t.[LoanPeriod],t.[TotalLoanAmount],t.[ExecutionDate],t.[CheckNumber],t.[CheckDate],t.[GuarantorAddress]
	FROM  @LoanExecution_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanExecution_Tracking s
        WHERE t.LoanExecutionID = s.LoanExecutionID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanExecution]
	SET [LoanExecution].[LoanID] = t.[LoanID],[LoanExecution].[GuarantorName] = t.[GuarantorName],[LoanExecution].[LoanExecutionStatus] = t.[LoanExecutionStatus],[LoanExecution].[GuarantorSSN] = t.[GuarantorSSN],[LoanExecution].[InterestPercentage] = t.[InterestPercentage],[LoanExecution].[LoanPaymentMethodID] = t.[LoanPaymentMethodID],[LoanExecution].[LoanAmount] = t.[LoanAmount],[LoanExecution].[LoanPeriod] = t.[LoanPeriod],[LoanExecution].[TotalLoanAmount] = t.[TotalLoanAmount],[LoanExecution].[ExecutionDate] = t.[ExecutionDate],[LoanExecution].[CheckNumber] = t.[CheckNumber],[LoanExecution].[CheckDate] = t.[CheckDate],[LoanExecution].[GuarantorAddress] = t.[GuarantorAddress] 
	FROM [dbo].[LoanExecution] s JOIN @LoanExecution_Temp t 
    ON t.LoanExecutionID = s.LoanExecutionID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanExecution_Tracking r
				  WHERE t.LoanExecutionID = r.LoanExecutionID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanExecution]
    FROM @LoanExecution_Temp t JOIN [dbo].[LoanExecution] s
    ON t.LoanExecutionID = s.LoanExecutionID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











